package com.mall.manager.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.mall.common.dao.MongoDBManager;
import com.mall.common.dao.PhpsqlDBManager;
import com.mall.untils.DateUtils;
import com.mysql.jdbc.Connection;

@Service
public class Phpdatayizhi {

	@Resource
	private JdbcTemplate jdbcTemplate;
	/**
	 * 把宝宝的八大能力分数及总分导入到mongodb
	 */
	public void addbabyReviews(){
		Connection conn = PhpsqlDBManager.getConnection();
		String sql = "select s.baby_id,b.name,r.school,r.created_at,s.exam_id,s.type,s.score from evaluation.reviews_score s,evaluation.baby b,evaluation.reviews r where s.baby_id=b.id and r.baby_id=b.id";
		String sql1 ="select s.baby_id from evaluation.reviews_score s GROUP BY s.baby_id";
		PreparedStatement prep=null;
		PreparedStatement prep1=null;
		ResultSet rs=null;
		ResultSet rs1=null;
		List<Map<String, String>> queryForList= new ArrayList<Map<String, String>>();
		List<Map<String, String>> guopBabyId= new ArrayList<Map<String, String>>();
		try {
			prep = conn.prepareStatement(sql);
			rs= prep.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			Map<String, String> mps;
			while (rs.next()) {
				for (int i = 1; i <= col; i++) {
					mps= new HashMap<String, String>();
					mps.put("baby_id", rs.getString("baby_id"));
					mps.put("name", rs.getString("name"));
					mps.put("school", rs.getString("school"));
					mps.put("created_at", rs.getString("created_at"));
					mps.put("exam_id", rs.getString("exam_id"));
					mps.put("type", rs.getString("type"));
					mps.put("score", rs.getString("score"));
					queryForList.add(mps);
				}
			}
			
			prep1 = conn.prepareStatement(sql1);
			rs1= prep1.executeQuery();
			int col1 = rs.getMetaData().getColumnCount();
			Map<String, String> mps1;
			while (rs1.next()) {
				for (int i = 1; i <= col1; i++) {
					mps1= new HashMap<String, String>();
					mps1.put("baby_id", rs.getString("baby_id"));
					guopBabyId.add(mps1);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				prep.close();
				prep1.close();
				rs.close();
				rs1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		
		
		List<Map<String, Object>> list_new=new ArrayList<Map<String, Object>>();
		Map<String, Object> map_new=null;
		
		//循环第一列
		for (Map<String, String> mapold : guopBabyId) {
			if(null!=map_new && map_new.size()>0){
				list_new.add(map_new);
			}
			map_new =new  HashMap<String, Object>();
			map_new.put("motion", 0);// 运动
			map_new.put("mathematics", 0);// 数学
			map_new.put("social", 0);// 社交
			map_new.put("fine", 0);// 精细
			map_new.put("themselves", 0);// 自理
			map_new.put("art", 0);// 艺术
			map_new.put("cognition", 0);// 认知
			map_new.put("language", 0);// 语言
			map_new.put("totalScore", 0);// 总得分
			map_new.put("ut", DateUtils.getNow());
			//第一条数据
			for (Map<String, String> mapnew : queryForList) {
				Object object = mapold.get("baby_id");
				Object objectnewObject = mapnew.get("baby_id");
				if(object.equals(objectnewObject)){
					map_new.put("baby_id",object); //宝宝id
					map_new.put("shool",  mapnew.get("school"));//宝宝学校
					map_new.put("ct",  mapnew.get("created_at"));//回答问题时间
					String typeValue = mapnew.get("type").toString();
					if(typeValue.equals("大运动能力")){
						map_new.put("motion", mapnew.get("score"));// 运动
					}
					if(typeValue.equals("数学能力")){
						map_new.put("mathematics", mapnew.get("score"));// 数学
					}
					if(typeValue.equals("社交能力")){
						map_new.put("social", mapnew.get("score"));// 社交
					}
					if(typeValue.equals("精细动作")){
						map_new.put("fine", mapnew.get("score"));// 精细
					}
					if(typeValue.equals("自理能力")){
						map_new.put("themselves", mapnew.get("score"));// 自理
					}
					if(typeValue.equals("艺术能力")){
						map_new.put("art", mapnew.get("score"));// 艺术
					}
					if(typeValue.equals("认知能力")){
						map_new.put("cognition", mapnew.get("score"));// 认知
					}
					if(typeValue.equals("语言能力")){
						map_new.put("language", mapnew.get("score"));// 语言
					}
					if(typeValue.equals("总分")){
						map_new.put("totalScore", mapnew.get("score"));// 总得分
					}
				}
			}
		}
		if(null!=map_new && map_new.size()>0){
			list_new.add(map_new);
		}
		MongoDBManager.insertBatch("php_reviews", list_new);
	}

	/**
	 * 导入学校
	 */
	public void addSchool(){
		Connection conn = PhpsqlDBManager.getConnection();
		String sql ="select id,name,created_at,province,city,area,address,schoolmaster,tester from evaluation.school";
		List<Map<String,Object>> queryForList = new ArrayList<Map<String,Object>>();
		Map<String,Object> mp = new HashMap<String, Object>();
		PreparedStatement prep=null;
		ResultSet rs =null;
		try {
			prep = conn.prepareStatement(sql);
			rs = prep.executeQuery();
			while (rs.next()) {
				mp.put("id",rs.getString("id"));
				mp.put("name",rs.getString("name"));
				mp.put("created_at",rs.getString("created_at"));
				mp.put("province",rs.getString("province"));
				mp.put("city",rs.getString("city"));
				mp.put("area",rs.getString("area"));
				mp.put("address",rs.getString("address"));
				mp.put("schoolmaster",rs.getString("schoolmaster"));
				mp.put("tester",rs.getString("tester"));
				mp.put("ct", rs.getString("created_at"));
				mp.put("ut", DateUtils.getNow());
				queryForList.add(mp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				prep.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		MongoDBManager.insertBatch("php_school", queryForList);
		
	}
	/*public void addSchool1(){
		String sql ="select id,name,created_at,province,city,area,address,schoolmaster,tester from evaluation.school";
		List<Map<String,Object>> queryForList = jdbcTemplate.queryForList(sql);
		for (int i = 0; i < queryForList.size(); i++) {
			queryForList.get(i).put("ct", queryForList.get(i).get("created_at"));
			queryForList.get(i).put("ut", DateUtils.getNow());
			queryForList.get(i).remove("created_at");
		}
		MongoDBManager.insertBatch("php_school", queryForList);
	}*/
	
	/**
	 * 导入班级
	 */
/*	public void addClass(){
		String sql = "select * from evaluation.class";
		List<Map<String,Object>> queryForList = jdbcTemplate.queryForList(sql);
		for (int i = 0; i < queryForList.size(); i++) {
			queryForList.get(i).put("ct", DateUtils.getNow());
			queryForList.get(i).put("up", DateUtils.getNow());
		}
		MongoDBManager.insertBatch("php_class", queryForList);
		
		
	}
*/	
	
	public void addClass(){
		Connection conn = PhpsqlDBManager.getConnection();
		String sql = "select * from evaluation.class";
		List<Map<String,Object>> queryForList = new ArrayList<Map<String,Object>>();
		Map<String,Object> mp = new HashMap<String, Object>();
		PreparedStatement prep=null;
		ResultSet rs=null;
		try {
			prep = conn.prepareStatement(sql);
			rs = prep.executeQuery();
			while (rs.next()) {
				mp.put("id",rs.getString("id"));
				mp.put("school_id",rs.getString("school_id"));
				mp.put("grade",rs.getString("grade"));
				mp.put("name",rs.getString("name"));
				mp.put("img",rs.getString("img"));
				mp.put("teacher_name",rs.getString("teacher_name"));
				mp.put("teacher_phone",rs.getString("teacher_phone"));
				mp.put("ct", DateUtils.getNow());
				mp.put("ut", DateUtils.getNow());
				queryForList.add(mp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				prep.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		MongoDBManager.insertBatch("php_class", queryForList);
	}
	
	
	/**
	 * 导入宝宝数据
	 */
	public void addBaby(){
		Connection conn = PhpsqlDBManager.getConnection();
		String sql = "select b.id,b.name,b.school_id,s.name school_name,b.class,b.class_name,b.created_at,b.gender,b.age_months from evaluation.baby b,evaluation.school s where b.school_id=s.id";
		List<Map<String,Object>> queryForList = new ArrayList<Map<String,Object>>();
		Map<String,Object> mp = new HashMap<String, Object>();
		PreparedStatement prep=null;
		ResultSet rs=null;
		try {
			prep = conn.prepareStatement(sql);
			rs = prep.executeQuery();
			while (rs.next()) {
				mp.put("id",rs.getString("id"));
				mp.put("name",rs.getString("name"));
				mp.put("school_id",rs.getString("school_id"));
				mp.put("name",rs.getString("name"));
				mp.put("school_name",rs.getString("school_name"));
				mp.put("class",rs.getString("class"));
				mp.put("class_name",rs.getString("class_name"));
				mp.put("created_at", rs.getString("created_at"));
				mp.put("gender",rs.getString("gender"));
				mp.put("age_months",rs.getString("age_months"));
				mp.put("ct", rs.getString("created_at"));
				mp.put("ut", DateUtils.getNow());
				queryForList.add(mp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				prep.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		MongoDBManager.insertBatch("php_baby", queryForList);
	}
	
}
